1. The Field of the Invention
The present invention relates to database systems and more specifically, to manners of utilizing summary tables with a database system that does not otherwise support summary tables.
2. The Relevant Technology
A summary table, otherwise known as a materialized view, is a pre-computed and stored result of a query. A standard view is a derived relation defined in terms of base relations. A view defines a function mapped from a set of base tables to a derived table. This function is typically recomputed every time the view is referenced. A view becomes a materialized view when the tuples generated by the view are stored in a database. Index structures can be built on the materialized view. Consequently, database accesses to the materialized view can be much faster than recomputing the view. A materialized view or summary table is thus like a cache, essentially embodying a copy of selected data that can be accessed quickly.
The data stored in a database is generally accessed using a query formatted in the SQL language native to most existing database systems. It is a primary objective in designing database systems to be able to service queries with the least cost, that is, in the lowest amount of time. One manner of decreasing query response time is with the use of summary tables. It is often the case that certain data can be accessed more quickly by accessing one or more summary tables in which copies of the data have been stored. Summary tables are generally quite small relative to the entire database, and scanning a summary table is much more efficient than scanning multiple relations of a database. Thus, one technique for speeding up query servicing is to maintain a plurality of summary tables and to selectively direct queries to the appropriate summary table for which the query can be most rapidly serviced. It would be advantageous to employ summary tables directed to the various heterogeneous database systems in query optimization.
As a further complication to the employment of summary tables in multiple database systems (MDBS), some database systems do not support summary tables. In order to provide more efficient query servicing, it is desirable to utilize summary tables in such an environment.
Additionally, it is considered by the inventors to be advantageous in some instances to be able to support the summary tables locally within the respective databases of an MDBS, rather than within a centralized database management program. The benefits of so doing include better performance for a class of queries that involve computation on large amounts of data but that yields relatively smaller result sets; the possibility for using the remote source""s replication utilities for maintaining the currency of the summary table; and the enablement of the remote database""s applications to take advantage of the summary table.
It would be further advantageous if a single central program could be employed to support and manage summary tables on a plurality of heterogeneous database systems, including where necessary, on databases that do not natively support summary tables.
Accordingly, a need exists for a distributed database system that capitalizes on the use of summary tables. To best utilize such a system, the capability should be provided to centrally generate, maintain, and query the summary tables. The system preferably makes provisions for communicating with database systems that do not support summary tables. Such a distributed database system and its method of use are disclosed herein.
The apparatus of the present invention has been developed in response to the present state of the art, and in particular, in response to the problems and needs in the art that have not yet been fully solved by currently available database management systems. Thus, it is an overall objective of the present invention to provide a distributed database apparatus, system, and article of manufacture that capitalizes on summary tables within a plurality of heterogeneous database tables.
The apparatus of the present invention has been developed in response to the present state of the art, and in particular, in response to the problems and needs in the art that have not yet been fully solved by currently available database management systems. Thus, it is an overall objective of the present invention to provide an apparatus, system, and article of manufacture for supporting summary tables within a database system that does not otherwise support summary tables.
To achieve the foregoing object, and in accordance with the invention as embodied and broadly described herein in the preferred embodiment, a central database management system, method, and article of manufacture are provided. The central database management system is preferably implemented with modules for execution by a processor. In one embodiment, the modules comprise a communication module configured to communicate with the database system and to initiate the generation of an unrecognized summary table corresponding to the database system. The modules preferably also comprise an identification module in communication with the communication module and configured to identify the unrecognized summary table as a summary table.
As used herein, unrecognized summary table is generally intended to mean a database table containing summary data that is not particularly recognized by the database system containing the data that is summarized by the summary table and/or hosting the summary table as being a summary table.
In one embodiment, the system of the present invention comprises a central database communications program (central program) in which the communication module and the identification module are located. The unrecognized summary table may be situated within the central program or may be located within the database system. Where the unrecognized summary table is generated within the central program, the database system may remain unaware of the existence of the unrecognized summary table.
The unrecognized summary table in one embodiment comprises summary data corresponding to a database of the database system and is recognized by the database system as a normal table and by the central program as a summary table.
The identification module may comprise a catalog within or in communication with the central program. In this embodiment, the central program is configured to list an alias of the unrecognized summary table within the catalog, and to list corresponding information within the catalog identifying the unrecognized summary table as a summary table.
In one embodiment, the central program also comprises a summary table creation module. Preferably, the summary table creation module is configured to initiate the generation of the unrecognized summary table within the database system. In so doing, the summary table creation module preferably transmits commands from the central program to the database system to instruct the database system to generate the unrecognized summary table. The commands may be, for instance, DDL commands. The DDL commands in one embodiment comprise a SQL command selecting entries from a table of the database system where a selected criterion is met.
The summary table creation module may be configured to receive from a user a summary table create command, and in response generate a SQL command and transmit the SQL command to the database system to instruct the database system to generate the unrecognized summary table. The summary table creation module may be further configured to assign the unrecognized summary table an alias and the central program may be configured to store the alias and information regarding the contents of the summary table within the catalog.
The system in one embodiment also comprises a query processing module. The query processing module is preferably configured to receive a query from a user and in response generate a query plan that directs SQL queries to the unrecognized summary table. Preferably, the query processing module is also configured to consider the contents of the unrecognized summary table and generate a query plan that directs a SQL query to the unrecognized summary table where doing so is considered most cost effective. The communication module is preferably configured to transmit the query plan to the database system.
An attendant method of use of the present invention in one embodiment comprises generating an unrecognized summary table and populating the summary table with data corresponding to a database system. The method also comprises identifying the unrecognized summary table as a summary table. In one embodiment, the unrecognized summary table is stored in a central program corresponding to the communication module and the identification module. The unrecognized summary table may also be stored local to the database system.
Where the unrecognized summary table is stored local to the database system, the database system preferably identifies the unrecognized summary table as a normal table, while the identification module identifies the unrecognized summary table as a summary table.
In one embodiment, a central program is provided. The database system may be local to the central program, (i.e., both stored on a commons storage device) or remote, communicating over a link such as a network. Preferably, the central program initiates the generating of an unrecognized summary table. The step of identifying the unrecognized summary table in this embodiment comprises listing an alias of the unrecognized summary table within a catalog of the central program and listing information within the catalog identifying the unrecognized summary table as a summary table.
In one embodiment, generating the unrecognized summary table comprises transmitting commands from the central program to the database system, and in response to the commands, the database system generating the unrecognized summary table. The commands may comprise DDL commands. Where the commands comprise DDL commands, the DDL commands may comprise a SQL command selecting entries from a table of the database system where a selected criterion is met.
In one embodiment, generating an unrecognized summary table comprises presenting to the central program from a user a summary table create command, and in response the central program generating a SQL command, transmitting the SQL command to the database system, and in response, the database system creating the unrecognized summary table. The method may further comprise assigning the unrecognized summary table an alias and storing the alias and information regarding the contents of the summary table within a catalog of the central program.
Additionally, generating an unrecognized summary table may comprise generating a summary table within the central program, the summary table containing summary data corresponding to the database system, the database system being unaware of the existence of the unrecognized summary table.
The method may also comprise querying the unrecognized summary table. This may include receiving into the central program a query from a user requesting data from the database system; considering the contents of the unrecognized summary table; generating a SQL query to be transmitted to the database system, the SQL query directed at least partially to the unrecognized summary table; and transmitting the SQL query to the database system.
In yet another aspect of the invention, an article of manufacture comprises a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform the above-described method.
These and other objects, features, and advantages of the present invention will become more fully apparent from the following description and appended claims, or may be learned by the practice of the invention as set forth hereinafter.